<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd">
<html lang="zh-CN"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta http-equiv="Content-Language" content="zh-CN"><link href="stylesheet.css" media="all" rel="stylesheet" type="text/css">
<title>例子</title>
<script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?d286c55b63a3c54a1e43d10d4c203e75"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script>
</head><body class="SECT1">
<div>
<table summary="Header navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><th colspan="5" align="center" valign="bottom">PostgreSQL 8.2.3 中文文档</th></tr>
<tr><td width="10%" align="left" valign="top"><a href="spi-visibility.html" accesskey="P">后退</a></td><td width="10%" align="left" valign="top"><a href="spi.html">快退</a></td><td width="60%" align="center" valign="bottom">章41. 服务器编程接口</td><td width="10%" align="right" valign="top"><a href="spi.html">快进</a></td><td width="10%" align="right" valign="top"><a href="reference.html" accesskey="N">前进</a></td></tr>
</table>
<hr align="LEFT" width="100%"></div>
<div class="SECT1"><h1 class="SECT1"><a name="SPI-EXAMPLES">41.5. 例子</a></h1>
<p>这是一个非常简单的 SPI 使用的例子。过程 <code class="FUNCTION">execq</code> 在其第一个参数里接收一个 SQL 命令，第二个参数接收一个行计数，用 <code class="FUNCTION">SPI_exec</code> 执行这个查询并且返回查询执行过的记录个数。你可以在 <tt class="FILENAME">src/test/regress/regress.c</tt> 和 <tt class="FILENAME">contrib/spi</tt> 里找更复杂的例子。</p>
<pre class="PROGRAMLISTING">#include "executor/spi.h"

int execq(text *sql, int cnt);

int
execq(text *sql, int cnt)
{
    char *command;
    int ret;
    int proc;

    /* 把给出的 text 对象转换成 C 字符串 */
    command = DatumGetCString(DirectFunctionCall1(textout,
                                                  PointerGetDatum(sql)));

    SPI_connect();

    ret = SPI_exec(command, cnt);

    proc = SPI_processed;
    /*
     * 如果取出了一些行，通过 elog(INFO) 打印它们
     */
    if (ret &gt; 0 &amp;&amp; SPI_tuptable != NULL)
    {
        TupleDesc tupdesc = SPI_tuptable-&gt;tupdesc;
        SPITupleTable *tuptable = SPI_tuptable;
        char buf[8192];
        int i, j;

        for (j = 0; j &lt; proc; j++)
        {
            HeapTuple tuple = tuptable-&gt;vals[j];

            for (i = 1, buf[0] = 0; i &lt;= tupdesc-&gt;natts; i++)
                snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
                        SPI_getvalue(tuple, tupdesc, i),
                        (i == tupdesc-&gt;natts) ? " " : " |");
            elog(INFO, "EXECQ: %s", buf);
        }
    }

    SPI_finish();
    pfree(command);

    return (proc);
}</pre>
<p>这个函数使用了调用习惯版本-0 ，为了是让例子更容易理解。在真实的应用里，你应该使用新的版本-1 的接口。</p>
<p>下面是你在把函数编译成共享库之后声明它的方法：</p>
<pre class="PROGRAMLISTING">CREATE FUNCTION execq(text, integer) RETURNS integer
    AS '<tt class="REPLACEABLE"><i>filename</i></tt>'
    LANGUAGE C;</pre>
<p>下面是一个会话例子：</p>
<pre class="PROGRAMLISTING">=&gt; SELECT execq('CREATE TABLE a (x integer)', 0);
 execq
-------
     0
(1 row)

=&gt; INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
=&gt; SELECT execq('SELECT * FROM a', 0);
INFO:  EXECQ:  0    -- execq 插入 0 行
INFO:  EXECQ:  1    -- execq 返回，被上层 INSERT 插入

 execq
-------
     2
(1 row)

=&gt; SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
 execq
-------
     1
(1 row)

=&gt; SELECT execq('SELECT * FROM a', 10);
INFO:  EXECQ:  0
INFO:  EXECQ:  1
INFO:  EXECQ:  2    -- 0 + 2, 就像声明的那样只插入了一行

 execq
-------
     3              -- 10 只是最大值，3 是真实的行数
(1 row)

=&gt; DELETE FROM a;
DELETE 3
=&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 0 1
=&gt; SELECT * FROM a;
 x
---
 1                  -- 在 (0)+1 里面没有行
(1 row)

=&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INFO:  EXECQ:  1
INSERT 0 1
=&gt; SELECT * FROM a;
 x
---
 1
 2                  -- 在 a+1 里面有一行
(2 rows)

-- 下面示范了改变数据可视性的规则

=&gt; INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
INFO:  EXECQ:  1
INFO:  EXECQ:  2
INFO:  EXECQ:  1
INFO:  EXECQ:  2
INFO:  EXECQ:  2
INSERT 0 2
=&gt; SELECT * FROM a;
 x
---
 1
 2
 2                  -- 2行 * 1 (x 在第一行)
 6                  -- 3 行 (2 + 1 刚刚插入的) * 2 (第二行里的 x)
(4 rows)               ^^^^^^
                      在不同的调用里 execq() 看到的东西不同</pre>
</div>
<div>
<hr align="LEFT" width="100%">
<table summary="Footer navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><td width="33%" align="left" valign="top"><a href="spi-visibility.html" accesskey="P">后退</a></td><td width="34%" align="center" valign="top"><a href="index.html" accesskey="H">首页</a></td><td width="33%" align="right" valign="top"><a href="reference.html" accesskey="N">前进</a></td></tr>
<tr><td width="33%" align="left" valign="top">数据改变的可视性</td><td width="34%" align="center" valign="top"><a href="spi.html" accesskey="U">上一级</a></td><td width="33%" align="right" valign="top">参考手册</td></tr>
</table>
</div>
</body></html>